package com.caiyi.financial.nirvana.sms.mapper;

import com.caiyi.financial.nirvana.sms.base.mapper.BaseMapper;
import com.caiyi.financial.nirvana.sms.entity.SmsMsg;
import com.caiyi.financial.nirvana.sms.querybean.MsgDayQueryBean;
import com.caiyi.financial.nirvana.sms.querybean.MsgMonthQueryBean;
import com.caiyi.financial.nirvana.sms.querybean.QueryMsgBean;
import com.caiyi.financial.nirvana.sms.vo.SmsAccountVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

@Mapper
public interface SmsMsgMapper extends BaseMapper<SmsMsg> {

    @Insert("insert into sms_day_account (day,account,msg_count,success_count,unknown_count,failure_count) SELECT t1.day,t1.account,t1.msgCount,ifnull(t1.successCount,0),ifnull(t1.unknownCount,0),ifnull(t1.failureCount,0) from (select date_format(date_add(now(),INTERVAL -1 MINUTE), '%Y-%m-%d') as day,account,count(1) as msgCount ,ifnull(sum(case when status=3 then 1 else 0 end),0) as successCount,ifnull(sum(case when status=1 or status=2 then 1 else 0 end),0) as unknownCount,ifnull(sum(case when status=4 or status=5 then 1 else 0 end),0) as failureCount FROM sms_msg where send_time > date_format(date_add(now(),INTERVAL -1 MINUTE),'%Y-%m-%d') and account=#{account} GROUP BY day) t1")
    int insertSmsDayAccountUser(@Param("account") String account);

    //先查询在更新
    @Select("SELECT date_format( date_add( now( ), INTERVAL - 1 MINUTE ), '%Y-%m-%d' ) AS day,account,count( 1 ) AS msgCount,ifnull( sum( CASE WHEN m.STATUS = 3 THEN 1 ELSE 0 END ), 0 ) AS successCount,ifnull( sum( CASE WHEN m.STATUS = 1 OR m.STATUS = 2 THEN 1 ELSE 0 END ), 0 ) AS unknownCount,ifnull( sum( CASE WHEN m.STATUS = 4 OR m.STATUS = 5 THEN 1 ELSE 0 END ), 0 ) AS failureCount FROM sms_msg m WHERE send_time >= date_format( date_add( now( ), INTERVAL - 1 MINUTE ), '%Y-%m-%d' )GROUP BY day,account ")
    List<Map> searchSmsDayAccount();

    @Update("update sms_day_account set msg_count=#{msgCount},success_count=#{successCount},unknown_count=#{unknownCount},failure_count=#{failureCount} where day = #{day} and account = #{account}")
    int updateSmsDayAccount(Map<String, String> params);

    @Select("select account from sms_msg where send_time > date_format(date_add(now(),INTERVAL -1 MINUTE),'%Y-%m-%d') GROUP BY account")
    List<String> selectAccount();

    @Select("select account from sms_day_account where day = date_format(date_add(now(),INTERVAL -1 MINUTE),'%Y-%m-%d')")
    List<String> selectSmsDayAccount();

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgListByParams")
    List<SmsMsg> selectMsgListByParams(QueryMsgBean bean);

    //首页当天统计
    @SelectProvider(type = MsgSqlProvider.class, method = "getMsgByDayByAccount")
    Map getMsgByDayByAccount(@Param("account") String account);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByDayByAccount")
    List<SmsAccountVo> selectMsgByDayByAccount(MsgDayQueryBean bean);

    @SelectProvider(type = MsgSqlProvider.class, method = "selectMsgByMonthByAccount")
    List<SmsAccountVo> selectMsgByMonthByAccount(MsgMonthQueryBean bean);

    class MsgSqlProvider {
        public String selectMsgListByParams(QueryMsgBean bean) {
            return new SQL() {
                {
                    SELECT("sms_msg.id,sms_msg.batch_id as batchId,sms_msg.message_id as messageId,sms_msg.province,sms_msg.city,sms_msg.phone,sms_msg.operator*1 as operator,sms_msg.content," +
                            "sms_msg.gateway_id as gatewayId,sms_msg.ip,sms_msg.send_time as sendTime,case when sms_msg.status=1 or sms_msg.status=2 then null else  sms_msg.receive_time end as receiveTime,sms_msg.status*1 as status,sms_msg.access_number as accessNumber,sms_msg.result," +
                            "sms_msg.num,sms_msg.create_time as createTime,sms_msg.update_time as updateTime");
                    FROM("sms_msg");
                    if (StringUtils.isNotBlank(bean.getBatchId())) {
                        WHERE("sms_msg.batch_id=#{batchId}");
                    }
                    if (StringUtils.isNotBlank(bean.getMessageId())) {
                        WHERE("sms_msg.message_id=#{messageId}");
                    }
                    if (StringUtils.isNotBlank(bean.getAccount())) {
                        WHERE("sms_msg.account=#{account}");
                    }
                    if (StringUtils.isNotBlank(bean.getPhone())) {
                        WHERE("sms_msg.phone=#{phone}");
                    }
                    if (StringUtils.isNotBlank(bean.getGatewayId())) {
                        WHERE("sms_msg.gateway_id = #{gatewayId}");
                    }
                    if (bean.getOperator() != null) {
                        WHERE("sms_msg.operator=#{operator}");
                    }
                    if (StringUtils.isNotBlank(bean.getAccessNumber())) {
                        WHERE("sms_msg.access_number=#{accessNumber}");
                    }
                    if (bean.getStatus() != null) {
                        WHERE("sms_msg.status=#{status}");
                    }
                    if (StringUtils.isNotBlank(bean.getCity())) {
                        WHERE("sms_msg.city=#{city}");
                    }
                    if (StringUtils.isNotBlank(bean.getProvince())) {
                        WHERE("sms_msg.province=#{province}");
                    }
                    //if (bean.isSend()) {
                    if (bean.getStartTime() != null) {
                        WHERE("(sms_msg.send_time >= #{startTime} or sms_msg.send_time is null)");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("(sms_msg.send_time < date_add(#{endTime}, interval 1 day) or sms_msg.send_time is null)");
                    }
                    ORDER_BY("sms_msg.id desc");
                }
            }.toString();
        }

        public String selectMsgByDayByAccount(MsgDayQueryBean bean) {
            return new SQL() {
                {
                    SELECT(" date_format(day, '%Y-%m-%d') as day,(select company_name from sms_user t2 where t1.account = t2.account) as companyName," +
                            "sum(msg_count) as msgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount," +
                            "CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate");
                    FROM("sms_day_account t1");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day <= #{endTime}");
                    }
                    GROUP_BY("day");
                    ORDER_BY("day desc");
                }
            }.toString();

        }

        public String getMsgByDayByAccount() {
            return new SQL() {
                {
                    SELECT(" date_format(day, '%Y-%m-%d') as day,(select company_name from sms_user t2 where t1.account = t2.account) as companyName," +
                            "sum(msg_count) as msgCount,sum(success_count) as successCount,sum(unknown_count) as unknownCount,sum(failure_count) as failureCount," +
                            "CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate," +
                            "CONCAT(TRUNCATE(sum(failure_count)/sum(msg_count) * 100, 2),'%') as failureRate," +
                            "CONCAT(TRUNCATE(sum(unknown_count)/sum(msg_count) * 100, 2),'%') as unknownRate");
                    FROM("sms_day_account t1");
                    WHERE("day = DATE_FORMAT(NOW(),'%Y-%m-%d')");
                    WHERE("account = #{account}");
                    GROUP_BY("day");
                }
            }.toString();

        }

        public String selectMsgByMonthByAccount(MsgMonthQueryBean bean) {
            return new SQL() {
                {
                    SELECT("date_format(day, '%Y-%m') as month,(select company_name from sms_user t2 " +
                            "where t1.account = t2.account) as companyName,sum(msg_count) as msgCount,sum(success_count) as successCount," +
                            "sum(unknown_count) as unknownCount,sum(failure_count) as failureCount,CONCAT(TRUNCATE(sum(success_count)/sum(msg_count) * 100, 2),'%') as successRate ");
                    FROM("sms_day_account t1");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account = #{account}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("day >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("day < date_add(#{endTime}, interval 1 month)");
                    }
                    GROUP_BY("month,account");
                    ORDER_BY("month desc");
                }
            }.toString();
        }
    }
}
